package mailserver.sql;

import java.sql.*;
import java.util.ArrayList;
import mailcommon.entities.*;

public class LetterService {

    private static LetterService instance = null;

    private LetterService() {
    }

    public static LetterService getInstance() {
        if (instance == null) {
            instance = new LetterService();
        }
        return instance;
    }

    /**
     * Selects records from letter table of DB.
     *
     * @param whereParams Query in format: "SELECT * FROM letter WHERE
     * <whereParams> ;". In case of given empty string query looks like "SELECT
     * * FROM letter;" and method returns all records from table.
     * @return ArrayList of Letter objects, generated from ResultSet of query.
     * @throws SQLException
     */
    public ArrayList<Letter> select(String whereParams)
            throws SQLException {
        ArrayList<Letter> letters = new ArrayList<Letter>();

        String query = "SELECT * "
                + " FROM letter"
                + (!whereParams.isEmpty() ? " WHERE " + whereParams : "")
                + ";";

        Connection con = DBController.getInstance().createConnection();
        try {
            PreparedStatement stmt = con.prepareStatement(query);
            ResultSet rs = stmt.executeQuery();

            Letter cur;
            while (rs.next()) {
                cur = new Letter(rs.getString("sender"), rs.getString("recipient"),
                        rs.getDate("date").toString(),
                        rs.getString("subject"), rs.getString("body"),
                        rs.getBoolean("old"), rs.getInt("id"));
                letters.add(cur);
            }

            stmt.close();
        } finally {
            con.close();
        }
        return letters;
    }
    
    public void markRead(int letterId) 
            throws SQLException {
        String query = "UPDATE letter "
             + "SET old = 1 "
             + "WHERE id = " + letterId + ";";
        
        Connection con = DBController.getInstance().createConnection();
        try {
            PreparedStatement stmt = con.prepareStatement(query);
            stmt.execute();
            stmt.close();
        } finally {
            con.close();
        }
    }

    public ArrayList<Letter> selectByRecipient(String recipientAddress)
            throws SQLException {
        ArrayList<Letter> letters = select(
                "recipient = \"" + recipientAddress + "\"");
        return letters;
    }
    
    public ArrayList<Letter> selectByRecipientUnread(String recipientAddress) 
            throws SQLException {
        ArrayList<Letter> letters = select(
                "recipient = \"" + recipientAddress + "\""
                + "AND old = 0");
        return letters;
    }

    public void add(Letter letter)
            throws SQLException {

        Connection con = DBController.getInstance().createConnection();
        try {
            String query = "insert into "
                    + "letter(sender,recipient,date,old,subject,body) "
                    + "values ("
                    + "\"" + letter.getSenderAddress() + "\","
                    + "\"" + letter.getRecipientAddress() + "\","
                    + "\"" + letter.getDateString() + "\","
                    + ((letter.isRead()) ? 1 : 0) + ","
                    + "\"" + letter.getSubject() + "\","
                    + "\"" + letter.getBody() + "\""
                    + ")";

            PreparedStatement stmt = con.prepareStatement(query);
            stmt.execute();
            stmt.close();
        } finally {
            con.close();
        }
    }
}
